package com.fridge.data;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import com.fridge.data.FridgeItem;
import com.fridge.data.ShoppingItem;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBHelper extends SQLiteOpenHelper {

    // Database Constant
    public static final int STATUS_DONE = 1;
    public static final int STATUS_DEFAULT = 0;
	
	
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "FridgeDB.sqlite";

    private static final String TABLE_FRIDGE = "fridge";
    private static final String TABLE_SHOPPING = "shopping";

    
    private static final String TABLE_BARCODE = "barcode";

    
    private static final String COLUMN_ID = "_id";
    private static final String COLUMN_NAME = "_name";
    private static final String COLUMN_BARCODE = "_barcode";

    
    
    private static final String COLUMN_EXPIRE = "_expire";
    private static final String COLUMN_PIC = "_pic";
    private static final String COLUMN_STATUS = "_status";
    
    private static final String COLUMN_QUANTITY = "_quantity";
    
	// ---------------------------------------------------------------
	//	DATABASE FUNCTION
	// ---------------------------------------------------------------

	public DBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION); 
    }

	@Override
	public void onCreate(SQLiteDatabase database) {
		

		String createBarcodeTable = "CREATE  TABLE " + TABLE_BARCODE + " (" +
				COLUMN_ID + " INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE ," +
				COLUMN_NAME + " CHAR, " +
				COLUMN_PIC + " CHAR, " +
				COLUMN_BARCODE + " CHAR " +
				")";

		
		
		String createFridgeTable = "CREATE  TABLE " + TABLE_FRIDGE + " (" +
				COLUMN_ID + " INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE ," +
				COLUMN_NAME + " CHAR, " +
				COLUMN_EXPIRE + " DATETIME NOT NULL , " +
				COLUMN_PIC + " CHAR, " +
				COLUMN_STATUS + " INTEGER " +
				")";
		

		String createShoppingTable = "CREATE  TABLE " + TABLE_SHOPPING + " (" +
				COLUMN_ID + " INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE ," +
				COLUMN_NAME + " CHAR, " +
				COLUMN_QUANTITY + " INTEGER, " +
				COLUMN_PIC + " CHAR, " +
				COLUMN_STATUS + " INTEGER " +
				")";
		
//		Log.d("create: ", createFridgeTable);
//		Log.d("create: ", createShoppingTable);
		
		
		database.execSQL(createFridgeTable);
		database.execSQL(createShoppingTable);
		database.execSQL(createBarcodeTable);
			
	}
	
	public void closeDB() {
        SQLiteDatabase db = this.getReadableDatabase();
        if (db != null && db.isOpen())
            db.close();
    }

	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
		// TODO Auto-generated method stub
		
	}
	

	// ---------------------------------------------------------------
	//	BARCODE FUNCTION
	// ---------------------------------------------------------------
	
	

	public int addBarcodeItem(BarcodeItem item)
	{
		
	    SQLiteDatabase db = this.getWritableDatabase();	    
	    ContentValues values = new ContentValues();
	    values.put(COLUMN_NAME, item.getName());
	    values.put(COLUMN_ID, item.getBarcode());
	    values.put(COLUMN_PIC, item.getPic());
	    
	    int itemID = (int)db.insert(TABLE_FRIDGE, null, values);	 	    
	    item.setId(itemID);
	    
		return itemID;
	}

	
	
	public BarcodeItem getBarcodeItemFromBarcode(String barcode)
	{
	       String query = "SELECT  * FROM " + TABLE_BARCODE + " WHERE " + COLUMN_BARCODE + " = '" + barcode + "'";
	       SQLiteDatabase db = this.getWritableDatabase();
	       Cursor cursor = db.rawQuery(query, null);

	       int idIndex = cursor.getColumnIndex(COLUMN_ID);
     	   int nameIndex = cursor.getColumnIndex(COLUMN_NAME);
     	   int picIndex = cursor.getColumnIndex(COLUMN_PIC);

	       BarcodeItem item = null;
	       
	       if (cursor.moveToFirst()) 
	       {
	    	   item = new BarcodeItem();
	    	   item.setId(cursor.getInt(idIndex));
	    	   item.setName(cursor.getString(nameIndex));	               
	    	   item.setPic(cursor.getString(picIndex));
	       }
	       return item;
	}

	
	// ---------------------------------------------------------------
	//	FRIDGE FUNCTION
	// ---------------------------------------------------------------
	
	
	@SuppressLint("SimpleDateFormat")
	public int addFridgeItem(FridgeItem item)
	{
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
		
	    SQLiteDatabase db = this.getWritableDatabase();	    
	    ContentValues values = new ContentValues();
	    values.put(COLUMN_NAME, item.getName());
	    values.put(COLUMN_EXPIRE, dateFormat.format(item.getExpire()));
	    values.put(COLUMN_PIC, item.getPic());
	    values.put(COLUMN_STATUS, item.getStatus());	    
	    	 
	    // insert row
	    int fridgeId = (int)db.insert(TABLE_FRIDGE, null, values);	 
	    item.setId(fridgeId);

		return fridgeId;
	}
	
	public int deleteFridgeItem(FridgeItem item) 
	{
		int rowEffect = 0;
	    SQLiteDatabase db = this.getWritableDatabase();
	    rowEffect = db.delete(TABLE_FRIDGE, COLUMN_ID + " = ?", new String[] { String.valueOf(item.getId()) });
	    return rowEffect;
	}
	
	public FridgeItem getFridgeItemFromName(String name)
	{
	       String query = "SELECT  * FROM " + TABLE_FRIDGE + " WHERE " + COLUMN_NAME + " = '" + name + "'";
	       SQLiteDatabase db = this.getWritableDatabase();
	       Cursor cursor = db.rawQuery(query, null);

	       int idIndex = cursor.getColumnIndex(COLUMN_ID);
     	   int nameIndex = cursor.getColumnIndex(COLUMN_NAME);
     	   int expireIndex = cursor.getColumnIndex(COLUMN_EXPIRE);
     	   int statusIndex = cursor.getColumnIndex(COLUMN_STATUS);
     	   int picIndex = cursor.getColumnIndex(COLUMN_PIC);

	       FridgeItem item = null;
	       
	       if (cursor.moveToFirst()) 
	       {
	    	   item = new FridgeItem();
	    	   item.setId(cursor.getInt(idIndex));
	    	   item.setName(cursor.getString(nameIndex));	               
	    	   item.setPic(cursor.getString(picIndex));
	    	   item.setStatus(cursor.getInt(statusIndex));
	    	   item.setExpireString(cursor.getString(expireIndex));	               
	       }
	       return item;
	}
	
	
	public List<FridgeItem> getAllFridgeItem() {
	       List<FridgeItem> fridgeList = new LinkedList<FridgeItem>();
	 
	       String query = "SELECT  * FROM " + TABLE_FRIDGE + " ORDER BY " + COLUMN_EXPIRE;
	 
	       SQLiteDatabase db = this.getWritableDatabase();
	       Cursor cursor = db.rawQuery(query, null);
	 
     	   int idIndex = cursor.getColumnIndex(COLUMN_ID);
     	   int nameIndex = cursor.getColumnIndex(COLUMN_NAME);
     	   int expireIndex = cursor.getColumnIndex(COLUMN_EXPIRE);
     	   int statusIndex = cursor.getColumnIndex(COLUMN_STATUS);
     	   int picIndex = cursor.getColumnIndex(COLUMN_PIC);

	       FridgeItem item = null;

	       
	       if (cursor.moveToFirst()) {
	           do {
	        	   
	               item = new FridgeItem();
	               item.setId(cursor.getInt(idIndex));
	               item.setName(cursor.getString(nameIndex));	               
	               item.setPic(cursor.getString(picIndex));
	               item.setStatus(cursor.getInt(statusIndex));
	               item.setExpireString(cursor.getString(expireIndex));
	               	 
	               fridgeList.add(item);
	               
	           } while (cursor.moveToNext());
	       }
	 	 
	       return fridgeList;
	}
	
	public int moveFridgeItemToShopping(FridgeItem item, boolean merge)
	{
		// if found shopping list then do nothing
		if( merge )
		{
			ShoppingItem shoppingItem = this.getShoppingItemFromName(item.getName());
			if( shoppingItem != null)
				return 1;
		}
		ShoppingItem shoppingItem = new ShoppingItem();
		shoppingItem.setName(item.getName());
		shoppingItem.setStatus(STATUS_DEFAULT);
		shoppingItem.setPic(item.getPic());
		shoppingItem.setQuantity(1);
		
		this.deleteFridgeItem(item);
		return this.addShoppingItem(shoppingItem);
		
	}
	
	
	public int updateFridgeItem(FridgeItem item) {
	    SQLiteDatabase db = this.getWritableDatabase();
	 
	    ContentValues values = new ContentValues();
	    if(item.getExpire() != null)	    	
	    	values.put(COLUMN_EXPIRE, item.getExpireDateString());
	    if(item.getName() != null)
	    	values.put(COLUMN_NAME, item.getName());
	    
	    if(item.getPic() != null)
	    	values.put(COLUMN_PIC, item.getPic());

	    values.put(COLUMN_STATUS, item.getStatus());
	    
	    // updating row
	    return db.update(TABLE_FRIDGE, values, COLUMN_ID + " = ?",
	            new String[] { String.valueOf(item.getId()) });
	}
	
	
	// ---------------------------------------------------------------
	//	SHOPPING FUNCTION
	// ---------------------------------------------------------------

	public int addShoppingItem(ShoppingItem item)
	{
		
	    SQLiteDatabase db = this.getWritableDatabase();	    
	    ContentValues values = new ContentValues();
	    values.put(COLUMN_NAME, item.getName());
	    values.put(COLUMN_PIC, item.getPic());
	    values.put(COLUMN_STATUS, item.getStatus());	
	    values.put(COLUMN_QUANTITY, item.getQuantity());
	    	 
	    // insert row
	    int shoppingId = (int)db.insert(TABLE_SHOPPING, null, values);	 
	    item.setId(shoppingId);

		return shoppingId;
	}
	
	public int deleteShoppingItem(ShoppingItem item) 
	{
		int rowEffect = 0;
	    SQLiteDatabase db = this.getWritableDatabase();
	    rowEffect = db.delete(TABLE_SHOPPING, COLUMN_ID + " = ?", new String[] { String.valueOf(item.getId()) });
	    return rowEffect;
	}
	
	public List<ShoppingItem> getAllShoppingItem() {
	       List<ShoppingItem> shoppingList = new LinkedList<ShoppingItem>();
	 
	       String query = "SELECT  * FROM " + TABLE_SHOPPING;
	 
	       SQLiteDatabase db = this.getWritableDatabase();
	       Cursor cursor = db.rawQuery(query, null);
	 
	       int idIndex = cursor.getColumnIndex(COLUMN_ID);
	       int nameIndex = cursor.getColumnIndex(COLUMN_NAME);
	       int statusIndex = cursor.getColumnIndex(COLUMN_STATUS);
	       int picIndex = cursor.getColumnIndex(COLUMN_PIC);
	       int quantityIndex = cursor.getColumnIndex(COLUMN_QUANTITY);

	       ShoppingItem item = null;

	       
	       if (cursor.moveToFirst()) {
	           do {
	        	   
	               item = new ShoppingItem();
	               item.setId(cursor.getInt(idIndex));
	               item.setName(cursor.getString(nameIndex));	               
	               item.setPic(cursor.getString(picIndex));
	               item.setStatus(cursor.getInt(statusIndex));
	               item.setQuantity(cursor.getInt(quantityIndex));
	               	               	 
	               shoppingList.add(item);
	               
	           } while (cursor.moveToNext());
	       }
	 
	       Log.d("getAllShopping(): ", Integer.toString(shoppingList.size()));
	 
	       return shoppingList;
	   }

	
	public ShoppingItem getShoppingItemFromName(String name)
	{
	       String query = "SELECT  * FROM " + TABLE_SHOPPING + " WHERE " + COLUMN_NAME + " = '" + name + "'";
	       SQLiteDatabase db = this.getWritableDatabase();
	       Cursor cursor = db.rawQuery(query, null);

	       int idIndex = cursor.getColumnIndex(COLUMN_ID);
     	   int nameIndex = cursor.getColumnIndex(COLUMN_NAME);
     	   int statusIndex = cursor.getColumnIndex(COLUMN_STATUS);
     	   int picIndex = cursor.getColumnIndex(COLUMN_PIC);
     	   int quantityIndex = cursor.getColumnIndex(COLUMN_QUANTITY);

	       ShoppingItem item = null;

	       
	       if (cursor.moveToFirst()) 
	       {
	    	   item = new ShoppingItem();
	    	   item.setId(cursor.getInt(idIndex));
	    	   item.setName(cursor.getString(nameIndex));	               
	    	   item.setPic(cursor.getString(picIndex));
	    	   item.setStatus(cursor.getInt(statusIndex));
	    	   item.setQuantity(cursor.getInt(quantityIndex));
	       }
	       return item;
	}
	
	public void moveShoppingItemToFridge(ShoppingItem item, Date expire)
	{
		FridgeItem fridgeItem = new FridgeItem();
		fridgeItem.setName(item.getName());
		fridgeItem.setStatus(STATUS_DEFAULT);
		fridgeItem.setPic(item.getPic());
		fridgeItem.setExpire(expire);
		
		this.deleteShoppingItem(item);
		this.addFridgeItem(fridgeItem);
		
	}
	
	
	public int updateShoppingItem(ShoppingItem item) {
	    SQLiteDatabase db = this.getWritableDatabase();
	 
	    ContentValues values = new ContentValues();
	    if(item.getName() != null)
	    	values.put(COLUMN_NAME, item.getName());
	    
	    if(item.getPic() != null)
	    	values.put(COLUMN_PIC, item.getPic());

	    values.put(COLUMN_STATUS, item.getStatus());
	    values.put(COLUMN_QUANTITY, item.getQuantity());
	    
	    // updating row
	    return db.update(TABLE_SHOPPING, values, COLUMN_ID + " = ?",
	            new String[] { String.valueOf(item.getId()) });
	}
}